package uwosh.cs342.project1;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final int databaseVersion = 1;
	private static final String dbName = "contactList";
	private SQLiteDatabase cldb;
	private String tableName = "contacts";

	/**Constructor for the DB Helper*/
	public DatabaseHelper(Context context) {
		/** context needs the parent name so contact.this */
		super(context, dbName, null, databaseVersion);
	}

	/**For first call to make the data base*/
	@Override
	public void onCreate(SQLiteDatabase db) {
		String createContactsTable = "CREATE TABLE contacts"
				+ " (autoInc INTEGER PRIMARY KEY AUTOINCREMENT, namef TEXT, namem TEXT, namel TEXT, address TEXT,"
				+ " cell INTEGER, home INTEGER, alt INTEGER, email TEXT, comments TEXT);";
		db.execSQL(createContactsTable);
		addGroup("General Contacts");
	}
	

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
		db.setVersion(newVersion);
		
	}
	
	//========================================COMPLETED======================================
	
	
	/**Method to open the database to be used*/
	public void open(){
		cldb = this.getWritableDatabase();
	}
	
	
	/**Method to close the database if in use*/
	public void close(){
		if(cldb != null){
			cldb.close();
		}
	}
			
	/**Method to Add a New Contact*/
	public long insertContact(String groupName, ContactObject co){
		long id = 0;
		ContentValues cvs = new ContentValues();
		
	
		//Put all the values for a contact into the required ContentValue
		cvs.put("namef", co.nameF);
		cvs.put("namem", co.nameM);
		cvs.put("namel", co.nameL);
		cvs.put("address", co.address);
		cvs.put("cell", co.cell);
		cvs.put("home", co.home);
		cvs.put("alt", co.alt);
		cvs.put("email", co.email);
		cvs.put("comments", co.comments);
		
		//open/insert/close DB
		open();
		id = cldb.insert(tableName, null, cvs);
		close();
		
		addToGroup(groupName,id);
		
		return id;
	}
	
	/**For when adding a contact, it will add them to
	 * the main list and what ever group needs their FK
	 * */
	public void addToGroup(String groupName, long id){
		ContentValues cvs = new ContentValues();
		
		cvs.put("ugh",id);
		
		open();
		cldb.insert(groupName, null, cvs);
		close();
	}

	/**Deletes a contact from their group and from the main table
	 * Complete delete
	 * NO MEMORY LEAK*/
	public void deleteContact(long id,String groupName){
		open();
		cldb.delete(groupName, "_id="+id, null);
		cldb.delete(tableName, "_id="+id, null);
		close();
	}

	/**Add in a new group*/
	public void addGroup(String groupName){
		String createGroupTable = "CREATE TABLE "+groupName+"("
				+"ugh INTEGER, FOREIGN KEY (ugh) REFERENCES contacts (autoInc));";
		cldb.execSQL(createGroupTable);
		this.onUpgrade(cldb, cldb.getVersion(), cldb.getVersion()+1);
	}
	
	/**Edits an existing contact*/
	public void edit(long id, ContactObject co){
		ContentValues cvs = new ContentValues();
		
	
		//Put all the values for a contact into the required ContentValue
		if (co.nameF != null);
		cvs.put("namef", co.nameF);
		if (co.nameF != null);
		cvs.put("namem", co.nameM);
		if (co.nameF != null);
		cvs.put("namel", co.nameL);
		if (co.address != null);
		cvs.put("address", co.address);
		if (co.cell > 0);
		cvs.put("cell", co.cell);
		if (co.home > 0);
		cvs.put("home", co.home);
		if (co.alt > 0);
		cvs.put("alt", co.alt);
		if (co.email != null);
		cvs.put("email", co.email);
		if (co.comments != null);
		cvs.put("comments", co.comments);
		
		//open/insert/close DB
		open();
		cldb.update(tableName, cvs, "_id="+id , null);
		close();
	}
	
	/**When removing from a group it will move all FKs to the gen list*/
	public void removeGroup(String groupName){
		emptyTable(groupName);
		
		String drop = "DROP TABLE "+groupName;
		
		cldb.execSQL(drop);
		
	}
	
	/**Before removing a group make sure all contacts in the group are moved to gen*/
	public void emptyTable(String groupName){
		String query = "INSERT INTO General Contacts SELECT * FROM "+groupName;
		cldb.execSQL(query);
	}
	
	/**Call to get all of a tables contained IDs
	 * @return */
	public ContactObject getContact(long id){
		ContactObject co = new ContactObject();
		Cursor c;
		
		String query = "SELECT * FROM contacts WHERE autoInc = "+id;
		
		c = cldb.rawQueryWithFactory(null, query, null, null);
		
		co.nameF = c.getString(1);
		co.nameM = c.getString(2);
		co.nameL = c.getString(3);
		co.address = c.getString(4);
		co.cell = c.getInt(5);
		co.home = c.getInt(6);
		co.alt = c.getInt(7);
		co.email = c.getString(8);
		co.comments = c.getString(9);
		
		return co;
	}
	//=======================================================================================
	
	/**For initial builds of the lists for each group*/
	public long[] groupsContacts(String groupName){
		String getRows = "SELECT COUNT(*) FROM"+groupName;
		SQLiteStatement state = cldb.compileStatement(getRows);
		long rows = state.simpleQueryForLong();
		
		long [] cts = new long[(int) rows];
		Cursor c = null;
		c.moveToFirst();
		String query = "SELECT * FROM "+groupName;

		
		for (int i = 0; i < rows; i++){
			c = cldb.rawQueryWithFactory(null, query, null, null);
			cts[i] = c.getLong(0);
			c.moveToNext();
		}
		
		return cts;
	}

}